package zy.dao.batch.report.impl;

import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.batch.report.BatchReportDAO;
import zy.dto.batch.order.BatchOrderDetailReportDto;
import zy.dto.batch.sell.SellDetailReportDto;
import zy.dto.batch.sell.SellDetailSizeReportDto;
import zy.dto.batch.sell.SellReportCsbDto;
import zy.dto.batch.sell.SellReportDto;
import zy.dto.batch.sell.SellSummaryDto;
import zy.util.CommonUtil;
import zy.util.StringUtil;

@Repository
public class BatchReportDAOImpl extends BaseDaoImpl implements BatchReportDAO{
	
	private String getSellReportSQL(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("se_client_code"))){
			sql.append(" AND se_client_code = :se_client_code ");
		}
		if(StringUtil.isNotEmpty(params.get("se_depot_code"))){
			sql.append(" AND se_depot_code = :se_depot_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("se_manager"))){
			sql.append(" AND se_manager = :se_manager ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND se_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND se_ar_date <= :enddate ");
		}
		if(StringUtil.isNotEmpty(params.get("se_type"))){
			sql.append(" AND se_type = :se_type ");
		}
		sql.append(" AND se_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public Map<String, Object> countsumSellReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" COUNT(1) AS totalCount,");
		sql.append(" SUM(IF(se_type = 0,sel_amount,0)) AS amount,");
		sql.append(" SUM(IF(se_type = 0,sel_amount*sel_unitprice,0)) AS money,");
		sql.append(" SUM(IF(se_type = 1,ABS(sel_amount),0)) AS amount_th,");
		sql.append(" SUM(IF(se_type = 1,ABS(sel_amount)*sel_unitprice,0)) AS money_th,");
		sql.append(" SUM(sel_amount*sel_retailprice) AS sell_money,");
		sql.append(" SUM(sel_amount*sel_costprice) AS cost_money");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_selllist sel ON sel_number = se_number AND sel.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = sel_pd_code AND pd.companyid = sel.companyid");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getSellReportSQL(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<SellReportDto> listSellReport(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT sel_id AS id,");
		sql.append(" SUM(IF(se_type = 0,sel_amount,0)) AS amount,");
		sql.append(" SUM(IF(se_type = 0,sel_amount*sel_unitprice,0)) AS money,");
		sql.append(" SUM(IF(se_type = 1,ABS(sel_amount),0)) AS amount_th,");
		sql.append(" SUM(IF(se_type = 1,ABS(sel_amount)*sel_unitprice,0)) AS money_th,");
		sql.append(" SUM(sel_amount*sel_retailprice) AS sell_money,");
		sql.append(" SUM(sel_amount*sel_costprice) AS cost_money,");
		if("brand".equals(type)){
			sql.append(" pd_bd_code AS code,");
			sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS name");
		}else if("type".equals(type)){
			sql.append(" pd_tp_code AS code,");
			sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS name");
		}else if("client".equals(type)){
			sql.append(" se_client_code AS code,ci_name AS name");
		}else if("product".equals(type)){
			sql.append(" sel_pd_code AS code,pd_name AS name,pd_no");
		}else if("season".equals(type)){
			sql.append(" pd_season AS code,pd_season AS name");
		}else if("depot".equals(type)){
			sql.append(" se_depot_code AS code,");
			sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = se_depot_code AND dp.companyid = t.companyid LIMIT 1) AS name");
		}else if("manager".equals(type)){
			sql.append(" se_manager AS code,se_manager AS name");
		}
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_selllist sel ON sel_number = se_number AND sel.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = sel_pd_code AND pd.companyid = sel.companyid");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getSellReportSQL(params));
		if("brand".equals(type)){
			sql.append(" GROUP BY pd_bd_code");
		}else if("type".equals(type)){
			sql.append(" GROUP BY pd_tp_code");
		}else if("client".equals(type)){
			sql.append(" GROUP BY se_client_code");
		}else if("product".equals(type)){
			sql.append(" GROUP BY sel_pd_code");
		}else if("season".equals(type)){
			sql.append(" GROUP BY pd_season");
		}else if("depot".equals(type)){
			sql.append(" GROUP BY se_depot_code");
		}else if("manager".equals(type)){
			sql.append(" GROUP BY se_manager");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(SellReportDto.class));
	}

	@Override
	public Map<String, Object> countsumSellDetailReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" COUNT(1) AS totalCount,");
		sql.append(" SUM(sel_amount) AS sel_amount,");
		sql.append(" SUM(sel_amount*sel_unitprice) AS sel_unitmoney,");
		sql.append(" SUM(sel_amount*sel_retailprice) AS sel_retailmoney,");
		sql.append(" SUM(sel_amount*sel_costprice) AS sel_costmoney");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_selllist sel ON sel_number = se_number AND sel.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = sel_pd_code AND pd.companyid = sel.companyid");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getSellReportSQL(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<SellDetailReportDto> listSellDetailReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" sel_id,se_make_date,sel_number,sel_pd_code,sel_sub_code,sel_szg_code,sel_sz_code,sel_cr_code,sel_br_code,sel_amount,");
		sql.append(" sel_unitprice,sel_retailprice,sel_costprice,sel_remark,sel_type,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year,");
		sql.append(" ci_name AS client_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = se_depot_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = sel_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = sel_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = sel_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_selllist sel ON sel_number = se_number AND sel.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = sel_pd_code AND pd.companyid = sel.companyid");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getSellReportSQL(params));
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY sel_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(SellDetailReportDto.class));
	}
	
	@Override
	public List<String> sell_size_szgcode(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT sel_szg_code");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_selllist sel ON sel_number = se_number AND sel.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = sel_pd_code AND pd.companyid = sel.companyid");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getSellReportSQL(params));
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public Integer countSellDetailSizeReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1) FROM");
		sql.append(" (SELECT 1");
		sql.append(" sel_id,sel_pd_code,sel_szg_code,sel_cr_code,sel_br_code,");
		sql.append(" GROUP_CONCAT(CONCAT(sel_sz_code,':',sel_amount)) AS size_amount,SUM(sel_amount) AS totalamount,");
		sql.append(" sel_unitprice,sel_retailprice,sel_costprice,sel_type,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = sel_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = sel_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = sel_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_selllist sel ON sel_number = se_number AND sel.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = sel_pd_code AND pd.companyid = sel.companyid");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getSellReportSQL(params));
		sql.append(" GROUP BY sel_pd_code,sel_cr_code,sel_br_code");
		sql.append(")temp");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	
	@Override
	public List<SellDetailSizeReportDto> listSellDetailSizeReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" sel_id,sel_pd_code,sel_szg_code,sel_cr_code,sel_br_code,");
		sql.append(" GROUP_CONCAT(CONCAT(sel_sz_code,':',sel_amount)) AS size_amount,");
		sql.append(" SUM(sel_amount) AS totalamount,");
		sql.append(" SUM(sel_amount*sel_unitprice) AS unit_money,");
		sql.append(" SUM(sel_amount*sel_retailprice) AS sell_money,");
		sql.append(" SUM(sel_amount*sel_costprice) AS cost_money,");
		sql.append(" sel_unitprice,sel_retailprice,sel_costprice,sel_type,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = sel_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = sel_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = sel_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_selllist sel ON sel_number = se_number AND sel.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = sel_pd_code AND pd.companyid = sel.companyid");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getSellReportSQL(params));
		sql.append(" GROUP BY sel_pd_code,sel_cr_code,sel_br_code");
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY sel_pd_code,sel_cr_code,sel_br_code");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(SellDetailSizeReportDto.class));
	}
	
	private String getOrderReportSQL(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("od_client_code"))){
			sql.append(" AND od_client_code = :od_client_code ");
		}
		if(StringUtil.isNotEmpty(params.get("od_depot_code"))){
			sql.append(" AND od_depot_code = :od_depot_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("od_manager"))){
			sql.append(" AND od_manager = :od_manager ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND od_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND od_ar_date <= :enddate ");
		}
		if(StringUtil.isNotEmpty(params.get("od_type"))){
			sql.append(" AND od_type = :od_type ");
		}
		sql.append(" AND od_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public Map<String, Object> countsumOrderDetailReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" COUNT(1) AS totalCount,");
		sql.append(" SUM(odl_amount) AS odl_amount,");
		sql.append(" SUM(odl_realamount) AS odl_realamount,");
		sql.append(" SUM(odl_amount-odl_realamount) AS gapamount,");
		sql.append(" SUM(odl_amount*odl_unitprice) AS odl_unitmoney,");
		sql.append(" SUM(odl_realamount*odl_unitprice) AS realmoney,");
		sql.append(" SUM((odl_amount-odl_realamount)*odl_unitprice) AS gapmoney");
		sql.append(" FROM t_batch_order t");
		sql.append(" JOIN t_batch_orderlist odl ON odl_number = od_number AND odl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = odl_pd_code AND pd.companyid = odl.companyid");
		sql.append(" JOIN t_batch_client ci ON ci_code = od_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getOrderReportSQL(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}
	
	@Override
	public List<BatchOrderDetailReportDto> listOrderDetailReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" odl_id,od_make_date,od_state,odl_number,odl_pd_code,odl_sub_code,odl_szg_code,odl_sz_code,odl_cr_code,odl_br_code,odl_amount,odl_realamount,");
		sql.append(" odl_unitprice,odl_retailprice,odl_costprice,odl_remark,odl_type,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year,");
		sql.append(" ci_name AS client_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = od_depot_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = odl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = odl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = odl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_batch_order t");
		sql.append(" JOIN t_batch_orderlist odl ON odl_number = od_number AND odl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = odl_pd_code AND pd.companyid = odl.companyid");
		sql.append(" JOIN t_batch_client ci ON ci_code = od_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getOrderReportSQL(params));
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY odl_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(BatchOrderDetailReportDto.class));
	}
	
	private String getSellSummarySQL(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("se_client_code"))){
			sql.append(" AND se_client_code = :se_client_code ");
		}
		if(StringUtil.isNotEmpty(params.get("se_depot_code"))){
			sql.append(" AND se_depot_code = :se_depot_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("se_manager"))){
			sql.append(" AND se_manager = :se_manager ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("year"))){
			sql.append(" AND YEAR(se_ar_date) = :year ");
		}
		if(StringUtil.isNotEmpty(params.get("month"))){
			sql.append(" AND MONTH(se_ar_date) = :month ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND se_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND se_ar_date <= :enddate ");
		}
		if(StringUtil.isNotEmpty(params.get("se_type"))){
			sql.append(" AND se_type = :se_type ");
		}
		sql.append(" AND se_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public Integer countSellSummary(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1) FROM");
		sql.append(" (SELECT 1");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_selllist sel ON sel_number = se_number AND sel.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = sel_pd_code AND pd.companyid = sel.companyid");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getSellSummarySQL(params));
		if("brand".equals(type)){
			sql.append(" GROUP BY pd_bd_code");
		}else if("type".equals(type)){
			sql.append(" GROUP BY pd_tp_code");
		}else if("client".equals(type)){
			sql.append(" GROUP BY se_client_code");
		}
		sql.append(" )temp");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	
	@Override
	public List<SellSummaryDto> listSellSummary(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT code,name,");
		sql.append(" GROUP_CONCAT(CONCAT(month,':',amount,'_',money)) AS month_amountmoney");
		sql.append(" FROM");
		sql.append(" (SELECT SUM(sel_amount) AS amount,");
		sql.append(" SUM(sel_amount*sel_unitprice) AS money,");
		sql.append(" MONTH(se_ar_date) AS month,");
		if("brand".equals(type)){
			sql.append(" pd_bd_code AS code,");
			sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS name");
		}else if("type".equals(type)){
			sql.append(" pd_tp_code AS code,");
			sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS name");
		}else if("client".equals(type)){
			sql.append(" ci_id AS id,");
			sql.append(" se_client_code AS code,ci_name AS name");
		}
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_selllist sel ON sel_number = se_number AND sel.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = sel_pd_code AND pd.companyid = sel.companyid");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getSellSummarySQL(params));
		if("brand".equals(type)){
			sql.append(" GROUP BY pd_bd_code,MONTH (se_ar_date)");
		}else if("type".equals(type)){
			sql.append(" GROUP BY pd_tp_code,MONTH (se_ar_date)");
		}else if("client".equals(type)){
			sql.append(" GROUP BY se_client_code,MONTH (se_ar_date)");
		}
		sql.append(" )temp");
		sql.append(" GROUP BY code");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(SellSummaryDto.class));
	}
	
	@Override
	public List<Map<String, Object>> type_level_sell(Map<String,Object> params){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_tp_code AS tp_code,MONTH(se_make_date) AS month,SUM(sel_amount) AS amount,SUM(sel_amount*sel_unitprice) AS money");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_selllist sel ON sel_number = se_number AND sel.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = sel_pd_code AND pd.companyid = sel.companyid");
		sql.append(" WHERE 1=1");
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("se_client_code"))){
			sql.append(" AND se_client_code = :se_client_code ");
		}
		sql.append(" AND YEAR(se_make_date) = :year");
		sql.append(" AND se_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY pd_tp_code,MONTH(se_make_date)");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params);
	}
	
	private String getSellReportSQL_csb(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("se_client_code"))){
			sql.append(" AND se_client_code = :se_client_code ");
		}
		if(StringUtil.isNotEmpty(params.get("se_depot_code"))){
			sql.append(" AND se_depot_code = :se_depot_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("se_manager"))){
			sql.append(" AND se_manager = :se_manager ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND se_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND se_ar_date <= :enddate ");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND sel_cr_code = :cr_code ");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND sel_sz_code = :sz_code ");
		}
		if(StringUtil.isNotEmpty(params.get("br_code"))){
			if("empty".equals(params.get("br_code"))){
				sql.append(" AND sel_br_code = '' ");
			}else {
				sql.append(" AND sel_br_code = :br_code ");
			}
		}
		sql.append(" AND se_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public List<SellReportCsbDto> listSellReport_csb(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT sel_id AS id,");
		sql.append(" SUM(sel_amount) AS amount,");
		sql.append(" SUM(sel_amount * sel_unitprice) AS money,");
		if("color".equals(type)){
			sql.append(" sel_cr_code AS code,");
			sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = sel_cr_code AND cr.companyid = t.companyid LIMIT 1) AS name");
		}else if("size".equals(type)){
			sql.append(" sel_sz_code AS code,");
			sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = sel_sz_code AND sz.companyid = t.companyid LIMIT 1) AS name");
		}else if("bra".equals(type)){
			sql.append(" sel_br_code AS code,");
			sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = sel_br_code AND br.companyid = t.companyid LIMIT 1) AS name");
		}
		
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_selllist sel ON sel_number = se_number AND sel.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = sel_pd_code AND pd.companyid = sel.companyid");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getSellReportSQL_csb(params));
		if("color".equals(type)){
			sql.append(" GROUP BY sel_cr_code");
		}else if("size".equals(type)){
			sql.append(" GROUP BY sel_sz_code");
		}else if("bra".equals(type)){
			sql.append(" GROUP BY sel_br_code");
		}
		sql.append(" ORDER BY amount DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(SellReportCsbDto.class));
	}
	
}
